<?php declare(strict_types=1);

namespace App\Services\Count;

use App\Enums\ReceiveModelEnum;
use App\Models\Bi\Bill;
use App\Models\Store;
use App\Models\StoreAllPayWayList;
use App\Models\User;
use App\Services\Count\UserService;
use App\Services\Mch\MchService;
use App\Services\PrintSqlService;
use App\Services\Store\StoreMchService;
use App\Util\ExeclUtil;
use App\Util\ListUtil;
use App\Util\WhereUtil;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;


class FinanceCountService
{


    protected function setWhere($query, array $where): void
    {
//        "agentName" => $request->get("agentName"),
//            "storeName" => $request->get("storeName"),
//            "payType" => $request->get("payType"),
//            "company" => $request->get("company"),
//            "money" => $request->get("money"),
//            "cashier" => $request->get("merchant_id"),
//
        $newWhere = WhereUtil::filterWhere($where);
        if (!empty($newWhere)) {
            $query->where($newWhere);
        }

        if (!empty($where["receive_model"])) {
            $query->where("receive_model", $where["receive_model"]);
        }

        if (!empty($where["agent_id"])) {
            //查询代理商
            if (is_array($where["agent_id"])) {
                $query->whereIn("agent_id", $where["agent_id"]);
            } else {
                $query->where("agent_id", $where["agent_id"]);
            }
        }

        if (!empty($where["agent1_id"])) {
            //查询代理商
            if ($where["agent1_id"] == 1) {
                $query->where("oem_id", $where["agent1_id"]);
            } else {
                $query->where("agent1_id", $where["agent1_id"]);
            }
        }

        if (!empty($where["store_pid"])) {
            if (is_array($where["store_pid"])) {
                $query->whereIn("store_pid", $where["store_pid"]);
            } else {
                $query->where("store_pid", $where["store_pid"]);
            }
        }

        if (!empty($where["store_id"])) {
            if (is_array($where["store_id"])) {
                $query->whereIn("store_id", $where["store_id"]);
            } else {
                $query->where("store_id", $where["store_id"]);
            }
        }

        if (!empty($where["payType"])) {
            $query->where("pay_type", $where["payType"]);
        }

        if (!empty($where["company"])) {
            $query->where("company", $where["company"]);
        }

        if (!empty($where["money"])) {
            $query->where("total_fee", $where["money"]);
        }

        if (!empty($where["cashier_id"])) {
            $query->where("cashier_id", $where["cashier_id"]);
        }


        if (!empty($where["pay_type"])) {
            $query->where("pay_type", $where["pay_type"]);
        }
        if (!empty($where["company"])) {
            $query->where("company", $where["company"]);
        }


        if (!empty($where["oem_id"])) {
            $query->where("oem_id", $where["oem_id"]);
        }


        if (!empty($where["agent2_id"])) {
            $query->where("agent2_id", $where["agent2_id"]);
        }

        if (!empty($where["agent3_id"])) {
            $query->where("agent3_id", $where["agent3_id"]);
        }

        if (!empty($where["agent4_id"])) {
            $query->where("agent4_id", $where["agent4_id"]);
        }

    }

    public function financeDayCount(array $where, bool $page = true): array
    {
        $query = DB::connection("bi")->table("bills");
        $query->where("month", $where["month"]);
        $this->setWhere($query, $where);
        $total = 0;
        if ($page) {
            $total = Bill::query()->fromSub($query, "tmp")->count();
        }

        $query->addSelect([
            DB::raw("sum(total_fee) as total_fee"),//交易金额
            DB::raw("sum(refund_fee) as refund_fee"),//退款金额
            DB::raw("sum(fee_amount) as fee_amount"),//手续费
            DB::raw("sum(total_num) as total_num"), //交易笔数
            DB::raw("sum(refund_num) as refund_num"),// 退款笔数
            DB::raw("sum(oem_fee) as oem_fee"),
            DB::raw("sum(agent1_fee) as agent1_fee"),
//            DB::raw("sum(agent2_fee) as agent2_fee"),
            "month", "store_pid", "store_id", "receive_model", "company", "pay_rate", "agent_rate"
        ])
            ->groupBy(["month", "store_pid", "store_id", "receive_model", "company", "pay_rate", "agent_rate"])
            ->orderBy("month", "desc");

        if ($page) {
            $lists = $query->offset((int)(($where["page"] - 1) * $where["pagesize"]))->limit((int)$where["pagesize"])->get()->toArray();
        } else {
            $lists = $query->get()->toArray();
        }
        //查询店铺ID
        $storeIds = array_column($lists, "store_id");

        $storeIds = array_merge($storeIds, array_column($lists, "store_pid"));

        $stores = Store::query()->select(["id", "pid", "user_id", "store_id", "store_name", "created_at"])
            ->whereIn("store_id", $storeIds)->get()->toArray();
        $stores = array_combine(array_column($stores, "store_id"), $stores);
        //查询总店
//        $storeSubId = [];
//        foreach ($stores as $storeTmp) {
//            if ($storeTmp["pid"] > 0) {
//                $storeSubId[] = $storeTmp["pid"];
//            }
//        }
//        $storeParents = Store::query()->select(["id", "pid", "user_id", "store_id", "store_name", "created_at"])->whereIn("id", $storeSubId)->get()->toArray();
//        $storeParents = array_combine(array_column($storeParents, "id"), $storeParents);

        //["账单日期", "代理商名称", "直属销售", "门店名称", "门店创建时间", "通道模式", "通道名称", "通道商户号", "交易金额", "退款金额", "手续费", "返佣金额"];
        $userLists = (new UserService())->getUserTree();
        foreach ($lists as $k => $bill) {
            $bill = collect($bill)->toArray();
            $bill["store"] = null;
            $bill["storeParent"] = null;
            $user_id = 0;
            if (isset($stores[$bill["store_id"]])) {
                $bill["store"] = $stores[$bill["store_id"]] ?? null;
                $user_id = $stores[$bill["store_id"]]["user_id"];
            }
            if (isset($stores[$bill["store_pid"]])) {
                $bill["storeParent"] = $stores[$bill["store_pid"]] ?? null;
            }

            //在这里处理店铺的级别逻辑
            $userNodes = ListUtil::prevGetTreeNode($userLists, ["id" => $user_id]);
            $userNodes = array_reverse($userNodes);
            $bill["agent_id"] = 0;
            $bill["scale_id"] = $bill["agent_id"];
            //agent1 取-1级
            //直属销售- store - user_id
            $column = "";
            if (count($userNodes) == 1) {
                $column = "oem_fee";
                $bill["agent_id"] = $userNodes[0]["id"] ?? 0;
            }

            if (count($userNodes) == 2) {
                $column = "agent1_fee";
                $bill["agent_id"] = $userNodes[1]["id"] ?? 0;
            }

            if (count($userNodes) >= 3) {
                $column = "agent1_fee";
                $bill["agent_id"] = $userNodes[1]["id"] ?? 0;
            }

            $bill["receive_model_name"] = "";
            switch ($bill["receive_model"]) {
                case 1:
                    $bill["receive_model_name"] = "服务费模式";
                    break;
                case 2:
                    $bill["receive_model_name"] = "代扣模式";
                    break;
                case 3:
                    $bill["receive_model_name"] = "通道模式";
                    break;
                case 4:
                    $bill["receive_model_name"] = "预充模式";
                    break;
            }
            //整理数据
            $bill["reward_fee"] = $bill[$column];
            $lists[$k] = $bill;
        }
        //在这里处理账户名称
        $userIds = [];
        $userIds = array_merge($userIds, array_column($lists, "agent_id"));
        $userIds = array_merge($userIds, array_column($lists, "scale_id"));
        $userIds = array_unique($userIds);
        $userInfos = User::query()->select(["id", "pid", "name"])->whereIn("id", $userIds)->get()->toArray();
        $userInfos = array_combine(array_column($userInfos, "id"), $userInfos);

        $companys = array_column($lists, "company");
        $companys = StoreAllPayWayList::query()->whereIn("company", $companys)->get()->toArray();
        $companys = array_combine(array_column($companys, "company"), $companys);
        //数据合并
        foreach ($lists as $k => $bill) {
            $bill["agent_name"] = $userInfos[$bill["agent_id"]]["name"] ?? "id:" . $bill["agent_id"];
            $bill["scale_name"] = $userInfos[$bill["scale_id"]]["name"] ?? "id:" . $bill["scale_id"];
            $bill["company_name"] = $companys[$bill["company"]]["company_desc"] ?? "";
            $bill["reward_rate"] = bcsub((string)$bill["pay_rate"], (string)$bill["agent_rate"], 2);

//            "receipt_amount" => 0,//实收  总收款-退款
//            "settlement_amount" => 0,//结算 总收款-总退款-手续费
            $bill["receipt_amount"] = bcsub((string)$bill["total_fee"], (string)$bill["refund_fee"], 2);
            $lists[$k] = $bill;
        }

        return [
            "total" => $total,
            "data" => $lists
        ];
    }


    public function financeMonthCount(array $where, bool $page = true): array
    {
        $query = DB::connection("bi")->table("bills");
        $this->setWhere($query, $where);
        $query->where("month", $where["month"]);
        $query->whereBetween("day", [$where["startDate"], $where["endDate"]]);
        $query->groupBy(["agent1_id", "agent2_id", "agent3_id", "agent4_id", "store_pid", "store_id", "receive_model", "company", "pay_rate", "agent_rate", "mch_no"]);
        $query->addSelect([
            DB::raw("sum(total_fee) as total_fee"),//交易金额
            DB::raw("sum(refund_fee) as refund_fee"),//退款金额
            DB::raw("sum(fee_amount) as fee_amount"),//手续费
            DB::raw("sum(total_num) as total_num"), //交易笔数
            DB::raw("sum(refund_num) as refund_num"),// 退款笔数
            DB::raw("sum(oem_fee) as oem_fee"),
            DB::raw("sum(agent1_fee) as agent1_fee"),
            DB::raw("sum(agent2_fee) as agent2_fee"),
            DB::raw("sum(agent3_fee) as agent3_fee"),
            DB::raw("sum(agent4_fee) as agent4_fee"),
//            DB::raw("sum(agent2_fee) as agent2_fee"),
            "agent1_id", "agent2_id", "agent3_id", "agent4_id",
            "store_pid", "store_id",
            "receive_model", "company",
            "pay_rate",
            "agent_rate", "mch_no"
        ]);
        //再次分组
//        Bill::query()->fromSub($query,"tmp")->groupBy("");
        $total = 0;
        if ($page) {
            $total = Bill::query()->fromSub($query, "tmp")->count();
        }

        if ($page) {
            $lists = $query->offset((int)(($where["page"] - 1) * $where["pagesize"]))->limit((int)$where["pagesize"])->get()->toArray();
        } else {
            $lists = $query->get()->toArray();
        }
        //查询店铺ID
        $storeIds = array_column($lists, "store_id");

        $storeIds = array_merge($storeIds, array_column($lists, "store_pid"));

        $stores = Store::query()->select(["id", "pid", "user_id", "store_id", "store_name", "created_at"])
            ->whereIn("store_id", $storeIds)->get()->toArray();
        $stores = array_combine(array_column($stores, "store_id"), $stores);
        //查询总店
        //获取代理商
        $agentIds = array_column($lists, "agent1_id");
        $agentIds = array_merge($agentIds, array_column($lists, "agent2_id"), array_column($lists, "agent3_id"),array_column($lists, "agent4_id"));
        $agent = User::query()->select(["id", "name"])->whereIn("id", array_unique($agentIds))->get()->toArray();
        $agent = array_combine(array_column($agent, "id"), $agent);
        //["账单日期", "代理商名称", "直属销售", "门店名称", "门店创建时间", "通道模式", "通道名称", "通道商户号", "交易金额", "退款金额", "手续费", "返佣金额"];
//        $userLists = (new UserService())->getUserTree();
        foreach ($lists as $k => $bill) {
            $bill = collect($bill)->toArray();
            $bill["store"] = null;
            $bill["storeParent"] = null;
            if (isset($stores[$bill["store_id"]])) {
                $bill["store"] = $stores[$bill["store_id"]] ?? null;
            }
            if (isset($stores[$bill["store_pid"]])) {
                $bill["storeParent"] = $stores[$bill["store_pid"]] ?? null;
            }
            $bill["oem_name"] = "顶级账号";
            $bill["agent1_id_name"] = $bill["agent2_id_name"] = $bill["agent3_id_name"] = "";
            if (isset($agent[$bill["agent1_id"]])) {
                $bill["agent1_id_name"] = $agent[$bill["agent1_id"]]["name"] ?? "";
            }
            if (isset($agent[$bill["agent2_id"]])) {
                $bill["agent2_id_name"] = $agent[$bill["agent2_id"]]["name"] ?? "";
            }
            if (isset($agent[$bill["agent3_id"]])) {
                $bill["agent3_id_name"] = $agent[$bill["agent3_id"]]["name"] ?? "";
            }

            if (isset($agent[$bill["agent4_id"]])) {
                $bill["agent4_id_name"] = $agent[$bill["agent4_id"]]["name"] ?? "";
            }

            $bill["receive_model_name"] = ReceiveModelEnum::$receiveModelMap[$bill["receive_model"]] ?? "";
            $lists[$k] = $bill;
        }
        //在这里处理账户名称
        $userIds = [];
        $userIds = array_merge($userIds, array_column($lists, "agent_id"));
        $userIds = array_merge($userIds, array_column($lists, "scale_id"));
        $userIds = array_unique($userIds);
        $userInfos = User::query()->select(["id", "pid", "name"])->whereIn("id", $userIds)->get()->toArray();
        $userInfos = array_combine(array_column($userInfos, "id"), $userInfos);

        $companys = array_column($lists, "company");
        $companys = StoreAllPayWayList::query()->whereIn("company", $companys)->get()->toArray();
        $companys = array_combine(array_column($companys, "company"), $companys);
        //数据合并
        foreach ($lists as $k => $bill) {
            $bill["date"] = date("Ym", strtotime($where["startDate"]));
            $bill["startDate"] = $where["startDate"];
            $bill["endDate"] = $where["endDate"];
            $bill["company_name"] = $companys[$bill["company"]]["company_desc"] ?? "";

            $bill["reward_rate"] = bcsub((string)$bill["pay_rate"], (string)$bill["agent_rate"], 2);
//            "receipt_amount" => 0,//实收  总收款-退款
//            "settlement_amount" => 0,//结算 总收款-总退款-手续费
            $bill["receipt_amount"] = bcsub((string)$bill["total_fee"], (string)$bill["refund_fee"], 2);
            //获取商户号
            $bill["mch_no_pid"] = "";
            $bill["mch_no_id"] = "";
            if (empty($bill["mch_no"])) {
                $mch_no_pid = $mch_no_id = "";
                $mch_no_id = StoreMchService::getMchNo((string)$bill["store_id"], (string)$bill["company"]);
                if (empty($mch_no_id)) {
                    $mch_no_pid = StoreMchService::getMchNo((string)$bill["store_pid"], (string)$bill["company"]);
                }
                $bill["mch_no_pid"] = $mch_no_pid;
                $bill["mch_no_id"] = $mch_no_id;
                $bill["mch_no"] = $mch_no_id;
                if (empty($bill["mch_no"])) {
                    $bill["mch_no"] = $mch_no_pid;
                }
            }
            $lists[$k] = $bill;
        }

        return [
            "total" => $total,
            "data" => $lists
        ];
    }

    public function financeMonthCountExport(array $where, bool $page = false): string
    {
        $result = $this->financeMonthCount($where, $page);

        $data = $result["data"];
        //在这里导出
        $title = [
            "账单日期", "平台", "一级代理商名称", "二级代理商名称", "三级代理商名称", "总店名称", "分店名称", "通道模式", "通道名称", "通道商户号",
            "皖付通有效交易金额", "支付费率", "一级成本费率", "一级返佣费率", "一级返佣金额", "二级返佣金额", "三级返佣金额"
        ];
        $value = [];
        //写入execl
        foreach ($data as $k => $v) {
            $value[] = [
                $v["date"],
                $v["oem_name"],
                $v["agent1_id_name"],
                $v["agent2_id_name"],
                $v["agent3_id_name"],
                $v["storeParent"]["store_name"] ?? "",
                $v["store"]["store_name"] ?? "",
                $v["receive_model_name"],
                $v["company_name"],
                $v["mch_no"],
                $v["receipt_amount"],
                $v["pay_rate"] . "%",
                $v["agent_rate"] . "%",
                bcsub((string)$v["pay_rate"], (string)$v["agent_rate"], 2) . "%",
                $v["agent1_fee"],
                $v["agent2_fee"],
                $v["agent3_fee"]
            ];
        }
        //写入excel
        $util = new ExeclUtil();
        $fileName = "对账账单" . ($where["month"] ?? date("Y-m-d")) . "_" . time() . ".csv";
        $filePath = public_path("/uploads/csv/" . date("Y/md", time()) . "/");
        if (!is_dir($filePath)) {
            mkdir($filePath, 0777, true);
        }
        $util->exportToExcel($filePath . $fileName, [], $title, $value);
        $url = $util->uploadOss($filePath . $fileName, $fileName);
        //删除文件
        if (file_exists($filePath . $fileName)) {
            unlink($filePath . $fileName);
        }
        //删除fileName
        return $url;
    }

    //每日统计
    public function staticBillLogList(array $where, $pageModel = true, array $column = [
        "agent1_id", "agent1_rate", "agent1_fee",
        "agent2_id", "agent2_rate", "agent2_fee",
        "agent3_id", "agent3_rate", "agent3_fee"
    ]): array
    {
//        PrintSqlService::listen();
        $countService = new UserBillCountService();
        list($list, $total) = $countService->staticBillLogList($where, $pageModel, $column);

        //在这里获取信息
        $agentIds = array_column($list, "agent1_id");
        $agentIds = array_merge($agentIds, array_column($list, "agent2_id"), array_column($list, "agent3_id"));
        $agentIds = array_unique($agentIds);
        $agent = User::query()->select(["id", "name"])->whereIn("id", $agentIds)->get()->toArray();
        $agent = array_combine(array_column($agent, "id"), $agent);
        foreach ($list as $k => $v) {
            $v["agent1_id_name"] = $v["agent2_id_name"] = $v["agent3_id_name"] = "";
            if (isset($agent[$v["agent1_id"]])) {
                $v["agent1_id_name"] = $agent[$v["agent1_id"]]["name"] ?? "";
            }
            if (isset($agent[$v["agent2_id"]])) {
                $v["agent2_id_name"] = $agent[$v["agent2_id"]]["name"] ?? "";
            }
            if (isset($agent[$v["agent3_id"]])) {
                $v["agent3_id_name"] = $agent[$v["agent3_id"]]["name"] ?? "";
            }
            $list[$k] = $v;
        }
        return [$list, $total];
    }

    public function staticBillLogListExport(array $where): string
    {
        list($list, $total) = $this->staticBillLogList($where, false);
        unset($total);
        //处理导出逻辑
        $title = [
            "日期", "订单号", "总店名称", "分店名称", "通道模式", "通道名称", "交易金额", "手续费", "支付费率",
            "一级代理名称", "一级代理成本费率", "一级代理商佣金",
            "二级代理名称", "二级代理成本费率", "二级代理商佣金",
            "三级代理名称", "三级代理成本费率", "三级代理商佣金",
            "类型",
        ];
        $value = [];
        //写入execl
        foreach ($list as $k => $v) {
            $value[] = [
                $v["day"],
                $v["out_trade_no"],
                $v["store_pid_name"] ?? "",
                $v["store_id_name"] ?? "",
                $v["receive_model_name"],
                $v["company_name"],
                $v["total_fee"],
                $v["fee_amount"],
                $v["rate"] . "%",
                $v["agent1_id_name"],
                $v["agent1_rate"] . "%",
                $v["agent1_fee"],

                $v["agent2_id_name"],
                $v["agent2_rate"] . "%",
                $v["agent2_fee"],

                $v["agent3_id_name"],
                $v["agent3_rate"] . "%",
                $v["agent3_fee"],

                ($v["status"] == 1 ? "支付" : "退款")
            ];
        }
        //写入excel
        $util = new ExeclUtil();
        $fileName = "对账明细" . ($where["month"] ?? date("Y-m-d")) . "_" . time() . ".csv";
        $filePath = public_path("/uploads/csv/" . date("Y/md", time()) . "/");
        if (!is_dir($filePath)) {
            mkdir($filePath, 0777, true);
        }
        $util->exportToExcel($filePath . $fileName, [], $title, $value);
        $url = $util->uploadOss($filePath . $fileName, $fileName);
        //删除文件
        if (file_exists($filePath . $fileName)) {
            unlink($filePath . $fileName);
        }
        //删除fileName
        return $url;
    }


}